package com.tuanzi.loan.core.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.ParseException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
public class XLSUtils {

    public static void convertStringToNumeric(HSSFWorkbook wb) {
        HSSFSheet sheet = wb.getSheetAt(0);
        DecimalFormat df = getDecimalFormat();
        for (Row r : sheet) {
            for (Cell c : r) {
                if (Cell.CELL_TYPE_STRING == c.getCellType()) {
                    String text = c.getStringCellValue();

                    if ("-".equals(text)) {
                        continue;
                    }

                    if (isNumeric(text)) {
                        try {
                            c.setCellValue(df.parse(text).doubleValue());
                        } catch (ParseException e) {
                            log.error(e.getMessage(), e);
                        }
                        c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    }
                }
            }
        }
    }

    private static boolean isNumeric(String str) {
        Pattern pattern = Pattern.compile("^-?[\\d,]+(.\\d+)?$");
        Matcher isNum = pattern.matcher(str);
        if (!isNum.matches()) {
            return false;
        }
        return true;
    }

    private static DecimalFormat getDecimalFormat() {
        DecimalFormatSymbols dfs = new DecimalFormatSymbols();
        dfs.setDecimalSeparator('.');
        dfs.setGroupingSeparator(',');
        dfs.setMonetaryDecimalSeparator('.');
        return new DecimalFormat("###,##0.00", dfs);
    }

}
